package com.sxj.corejava.code02_jdbc;

import com.sxj.corejava.entity.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @author 石小俊
 * @date 2023年11月2023/11/30日 19:00
 */
public class Test04_分页查询 {

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.print("请输入查询第几页的数据:");
        int pageNo = sc.nextInt();
        System.out.print("请输入每页显示几条数据:");
        int pageSize = sc.nextInt();
        List<User> users = selectPage(pageNo,pageSize);
        for(User user : users){
            System.out.println(user);
        }
    }

    public static List<User> selectPage(int pageNo,int pageSize){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> users = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","");
            String sql = new StringBuffer()
                    .append(" select id,username,password,phone,address ")
                    .append(" from t_user ")
                    .append(" limit ?,? ")
                    .toString();
            // 查询第pageNo页的数据,每页显示pageSize条
            // limit (pageNo - 1) * pageSize,pageSize
            ps = conn.prepareStatement(sql);
            ps.setInt(1,(pageNo - 1) * pageSize);
            ps.setInt(2,pageSize);
            rs = ps.executeQuery();
            while(rs.next()){
                User user = new User();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                users.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return users;
    }

}
